package jdbc1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.DBUtil;

public class StudentDao implements IStudentDao {
	// 3：写好StudentDao，实现必要的CRUD功能（包括分页）
	@Override
	public List<Student> findall() {
		List<Student> list = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from s_student";
		try {
			st = con.prepareStatement(sql);
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				// s.setBirthday(rs.getString("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;

	}

	// 增加
	@Override
	public int inster(Student s) {
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		// ResultSet rs = null;
		String sql = "insert into s_student (sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass )values(?,?,?,?,?,?,?,?,?,?,?) ";
		// sname=?, sex=?, telephone=?,qq=?,
		// wechat=?,gradeid=?,enterdate=?,fromcity=?,
		// birthday=?,address=?,pass=?
		try {
			st = con.prepareStatement(sql);

			st.setString(1, s.getSname());
			st.setString(2, s.getSex());
			st.setString(3, s.getTelephone());
			st.setString(4, s.getQq());
			st.setString(5, s.getWechat());
			st.setInt(6, s.getGradeid());
			st.setString(7, s.getEnterdate());
			st.setString(8, s.getFromcity());
			st.setDate(9, s.getBirthday());
			// st.setString(9, s.getBirthday());
			st.setString(10, s.getAddress());
			st.setString(11, s.getPass());
			return st.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(null, st, con);
		}
		return 0;

	}

	// 删除
	@Override
	public int delete(Student s) {
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		// ResultSet rs = null;
		String sql = "delete from s_student where stuno=?  ";
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, s.getStuno());
			return st.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(null, st, con);
		}
		return 0;

	}

	// 修改
	@Override
	public int update(Student s) {
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		// ResultSet rs = null;
		String sql = "update  s_student set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,address=?,pass=? where stuno=? ";
		try {
			// rs = st.executeQuery();
			st = con.prepareStatement(sql);

			st.setString(1, s.getSname());
			st.setString(2, s.getSex());
			st.setString(3, s.getTelephone());
			st.setString(4, s.getQq());
			st.setString(5, s.getWechat());
			st.setInt(6, s.getGradeid());
			st.setString(7, s.getEnterdate());
			st.setString(8, s.getFromcity());
			st.setDate(9, s.getBirthday());
			st.setString(10, s.getAddress());
			st.setString(11, s.getPass());
			st.setInt(12, s.getStuno());
			return st.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(null, st, con);
		}
		return 0;
	}

	/**
	 * pageNo页数 pageSize:页大小
	 */
	@Override
	public List<Student> paging(int pageNo, int pageSize) {
		List<Student> list = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from s_student limit ?,?";
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, (pageNo - 1) * pageSize);
			st.setInt(2, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;

	}

	// 查询：根据姓名 和手机号，以及地址做模糊查询
	// 有可能条件没有提供，如果不提供，忽略这个条件
	public List<Student> paging(String name, String phone, String addr,
			Long gradeid, String qq) {
		String sql = "select * from s_student where 1=1";
		// 放参数
		List<Object> params = new ArrayList<>(3);
		// 不可行
		// if(name!=null &&phone!=null && addr!=null) {
		// sql+=" where sanme like ";
		// }
		// int index = 0;

		// 是个思路，自己过滤sql关键字
		// if (name != null && !"".equals(name)) {
		// sql += " where sname like '%" + name + "%' ";
		//
		// }

		if (name != null && !"".equals(name)) {
			sql += " and sname like   ? ";
			params.add("%" + name + "%");

		}
		if (phone != null && !"".equals(phone)) {
			sql += " and telephone like   ? ";
			params.add("%" + phone + "%");
		}
		if (addr != null && !"".equals(addr)) {
			sql += " and address like   ? ";
			params.add("%" + addr + "%");
		}
		if (gradeid != null && !"".equals(gradeid) && (gradeid > 0)) {
			sql += " and gradeid =  ? ";

			params.add(gradeid);
		}
		if (qq != null && !"".equals(qq)) {
			sql += " and qq like   ? ";
			params.add("%" + qq + "%");

		}
		System.out.println(sql + ">>." + params);
		List<Student> list = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				st.setObject(i + 1, params.get(i));
			}
			// st.setString(1, "%" + name + "%");
			// st.setString(2, "%" + phone + "%");
			// st.setString(3, "%" + addr + "%");
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));

				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;
	}

	public int total(String name, String phone, String addr, Long gradeid,
			String qq) {
		// String sql = "select * from s_student where 1=1";
		String sql = "select count(*)  from s_student where 1=1";
		// 放参数
		List<Object> params = new ArrayList<>();
		// 不可行
		// if(name!=null &&phone!=null && addr!=null) {
		// sql+=" where sanme like ";
		// }
		// int index = 0;

		// 是个思路，自己过滤sql关键字
		// if (name != null && !"".equals(name)) {
		// sql += " where sname like '%" + name + "%' ";
		//
		// }

		if (name != null && !" ".equals(name)) {
			sql += " and sname like   ? ";
			params.add("%" + name + "%");

		}
		if (phone != null && !"".equals(phone)) {
			sql += " and telephone like   ? ";
			params.add("%" + phone + "%");
		}
		if (addr != null && !"".equals(addr)) {
			sql += " and address like   ? ";
			params.add("%" + addr + "%");
		}
		if (gradeid != null && !"".equals(gradeid) && (gradeid > 0)) {
			sql += " and gradeid =  ? ";

			params.add(gradeid);
		}
		if (qq != null && !"".equals(qq)) {
			sql += " and qq like   ? ";
			params.add("%" + qq + "%");

		}
		System.out.println(sql + ">>." + params);

		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;

		int total = 0;
		try {

			st = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				st.setObject(i + 1, params.get(i));
			}
			rs = st.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return total;

	}
}
